package com.upcode.weighing.util.excel;

import jxl.Workbook;
import jxl.format.*;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;

public class JXLExcelUtils {


    /**
     * JXL自定义导出
     * @param response
     * @param list
     * @param fileName
     * @param date
     */
    public static void writeExcel(HttpServletResponse response,List list,String fileName,String date)  {
        try {
            // 第一步创建excel文件,并决定excel的路径和文件名
            OutputStream os =  null;
            WritableWorkbook wwb = null;
            // 取得输出流
            os = response.getOutputStream();
            response.reset();// 清空输出流
            // 设定输出文件头
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + ".xls");
            // 定义输出类型
            response.setContentType("application/msexcel;charset=utf-8");

            // 建立excel文件
            wwb = Workbook.createWorkbook(os);


            // 第二部,创建工作簿,指定该工作簿的名称和位置'
            WritableSheet sheet = wwb.createSheet("sheet", 0);

            // 第三部,设置表格指定列的列宽
            sheet.setColumnView(0, 25);
            sheet.setColumnView(1, 18);
            sheet.setColumnView(2, 18);
            sheet.setColumnView(3, 20);

            // 第四部,往工作簿中插入数据,设定字体:微软雅黑,24,加粗
            // 创建字体对象
            WritableFont titleFont = new WritableFont(WritableFont.createFont("微软雅黑"), 24, WritableFont.NO_BOLD);
            WritableFont contentFont = new WritableFont(WritableFont.createFont("楷体 _GB2312"), 12, WritableFont.NO_BOLD);
            WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
            WritableCellFormat contentFormat = new WritableCellFormat(contentFont);
            WritableCellFormat contentFormat2 = new WritableCellFormat(contentFont);
            WritableCellFormat contentFormat3 = new WritableCellFormat(contentFont);
            WritableCellFormat contentFormat4 = new WritableCellFormat(contentFont);


            contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            contentFormat3.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

            // 设置格式居中对齐
            titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
            contentFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
            contentFormat2.setAlignment(jxl.format.Alignment.LEFT);
            contentFormat.setBackground(Colour.GREY_40_PERCENT);

            contentFormat4.setAlignment(jxl.format.Alignment.RIGHT);

            // 将定义好的单元格绑定数据添加到工作簿中
            sheet.mergeCells(0, 0, 3, 0); // 合并单元格A-D列共4列
            sheet.addCell(new Label(0, 0, fileName, titleFormat));
            sheet.mergeCells(0, 1, 2, 1); // 合并单元格A-C列共3列
            sheet.addCell(new Label(0, 1, "拆分时间："+date, contentFormat2));
            sheet.addCell(new Label(3, 1, "单位：元", contentFormat4));

            sheet.addCell(new Label(0, 2, "第三方支付渠道", contentFormat));
            sheet.addCell(new Label(0, 3, "收费站名称", contentFormat));
            sheet.mergeCells(1, 2, 1, 3); // 合并单元格3~4行
            sheet.addCell(new Label(1, 2, "微信", contentFormat));
            sheet.mergeCells(2, 2, 2, 3); // 合并单元格3~4行
            sheet.addCell(new Label(2, 2, "支付宝", contentFormat));
            sheet.mergeCells(3, 2, 3, 3); // 合并单元格3~4行
            sheet.addCell(new Label(3, 2, "合计", contentFormat));

            // 添加底层的信息
            sheet.mergeCells(0, 12, 3, 12); // 合并单元格A-C列共3列
            sheet.addCell(new Label(0, 12, "制表:系统生成                  对账:                审核:",
                    contentFormat2));

            // 第五部,写入工作表完毕,关闭流
            wwb.write();
            wwb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
    }

}
